package com.paas.form.visualdev.service.impl;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import com.paas.common.jdbc.JdbcMeta;
import com.paas.common.jdbc.JdbcSql;
import com.paas.common.jdbc.NameUtil;
import com.paas.common.jdbc.model.DbColumn;
import com.paas.common.jdbc.model.DbConfig;
import com.paas.common.jdbc.model.DbResult;
import com.paas.common.jdbc.model.DbTableVo;
import com.paas.common.jdbc.pageSql.PageSql;
import com.paas.common.jdbc.pageSql.PageSqlFactory;
import com.paas.common.model.RestResponse;
import com.paas.common.page.PageBean;
import com.paas.common.util.BeanUtil;
import com.paas.common.util.DateUtil;
import com.paas.common.util.StringUtil;
import com.paas.common.util.str.ParserException;
import com.paas.form.cache.IDataSourceCache;
import com.paas.form.core.element.field.ColumnDataModel;
import com.paas.form.core.element.field.ColumnListModel;
import com.paas.form.core.element.field.SearchListModel;
import com.paas.form.visualdev.FormVisualdevEntity;
import com.paas.form.visualdev.service.IFormVisualdevService;
import com.paas.form.visualdev.service.VisualDevDataService;
import com.paas.form.visualdev.util.*;
import com.paas.form.visualdev.vo.ListDataQuery;
import com.paas.plus.tool.form.model.JdbcInsertParams;
import com.paas.plus.tool.form.model.JdbcUpdateParams;
import com.paas.plus.tool.form.util.JdbcParamUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.xmlbeans.impl.xb.xsdschema.Public;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Timestamp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;


@Slf4j

@Service("visualDevDataService")
public class VisualDevDataServiceImpl implements VisualDevDataService {

    @Autowired
    public IFormVisualdevService formVisualdevService;

    @Autowired
    private IDataSourceCache dataSourceCache;

    private static String ID_FIELD = "id";

    private static String PARENT_FIELD = "parentId";

    private static String TEXT_FIELD = "name";

    private static String TOTAL_NUM = "TOTAL_NUM";


    @Override
    public PageBean<Map<String, Object>> getPageList(ListDataQuery listDataQuery) {
        if (listDataQuery == null || StringUtil.isEmpty(listDataQuery.getFormId())) {
            return null;
        }
        FormVisualdevEntity bean = formVisualdevService.getById(listDataQuery.getFormId());
        if(BeanUtil.isEmpty(bean)||StringUtil.isEmpty(bean.getDbLinkId())){
            return null;
        }
        DbConfig dbConfig = dataSourceCache.queryJdbcById(bean.getDbLinkId());
        if(BeanUtil.isEmpty(dbConfig)){
            return null;
        }

        ColumnDataModel columnDataModel = JSONObject.parseObject(bean.getColumnData(),ColumnDataModel.class);

        //列表字段
//        JSONArray columnListModel = JSONArray.parseArray(columnDataModel.getColumnList()) ;
//        List<ColumnListModel> columnListModelList = columnListModel.toList(ColumnListModel.class);
        List<ColumnListModel> columnListModelList = columnDataModel.getColumnList();
        //查询字段
//        JSONArray search = JSONArray.parseArray( columnDataModel.getSearchList());
//        List<SearchVo> searchColumnList = search.toList(SearchVo.class);
        List<SearchListModel> searchList = columnDataModel.getSearchList();
        List<SearchVo> searchColumnList = searchList.stream().map(searchBean ->{
            SearchVo searchVo =new SearchVo();
            searchVo.setColumnName(searchBean.getColumnName());
            searchVo.setColumnHumpName(searchBean.getColumnHumpName());
            searchVo.setRemarks(searchBean.getRemarks());
            return searchVo;
        }).collect(Collectors.toList());

        //主表名
        String masterTable=null;
        DbTableVo dbTableVo = null;
        JSONArray tableJsonArray  = JSONArray.parseArray(bean.getTemTable());
        if(BeanUtil.isNotEmpty(tableJsonArray)){
            for (int i = 0; i <tableJsonArray.size() ; i++) {
                JSONObject item = (JSONObject) tableJsonArray.get(i);
                if(item.get("typeId").equals(1)){
                    masterTable = item.get("tableName").toString();
//                    dbTableVo = JSONObject.parseObject(String.valueOf(item),DbTableVo.class);
                }
            }
        }

        //定义主sql
        StringBuffer mainSql = new StringBuffer();
        mainSql.append("select * ");
        //拼接表名
        mainSql.append(" from ").append(masterTable);
        List<SearchVo> searchVoList = null;
        //拼接查询条件
        if(StringUtil.isNotEmpty(listDataQuery.getQueryJson())){
            JSONObject queryObject = JSONObject.parseObject(listDataQuery.getQueryJson());
            searchVoList = searchColumnList.stream().map(searchVO -> {
                searchVO.setValue(queryObject.get(searchVO.getColumnName()).toString());
                return searchVO;
            }).filter(vo -> vo.getValue() != null).collect(Collectors.toList());
        }
        if(searchVoList != null && searchVoList.size() > 0){
            mainSql.append(" where 1=1 ");
            for (SearchVo searchVo:searchVoList) {
                mainSql.append(" and ").append(searchVo.getColumnName()).append("='").append(searchVo.getValue()).append("'");
            }
        }

        JdbcSql jdbcSql = new JdbcSql(dbConfig);

        //分页总记录数sql
        StringBuffer queryPageCountSql = new StringBuffer();
        queryPageCountSql.append("select count(1) as ");
        queryPageCountSql.append(TOTAL_NUM).append(" from (");
        queryPageCountSql.append(mainSql.toString());
        queryPageCountSql.append(") a");

        PageBean<Map<String, Object>> pageBean = new PageBean<Map<String, Object>>();
        pageBean.setCurrentPage(listDataQuery.getCurrentPage());
        pageBean.setPagesize(listDataQuery.getPageSize());

        //获取总记录数
        DbResult dbCountResult = jdbcSql.getSqlData(queryPageCountSql.toString());
        if (dbCountResult != null && dbCountResult.getData() != null) {
            List<Map<String, Object>> dataCountList = dbCountResult.getData();
            Map<String, Object> dataCountMap = dataCountList.get(0);

            if (dataCountMap.containsKey(TOTAL_NUM)) {
                Object totalNum = dataCountMap.get(TOTAL_NUM);
                pageBean.setTotalCount(Integer.parseInt(totalNum.toString()));
            }
        }

        //查询分页列表sql
        PageSql pageSql = PageSqlFactory.build(dbConfig);
        String queryPageListSql = pageSql.getSql(mainSql.toString(), listDataQuery.getCurrentPage(), listDataQuery.getPageSize());
        //获取当前页明细记录
        DbResult dbListResult = jdbcSql.getSqlData(queryPageListSql);
        if (dbListResult != null && dbListResult.getData() != null) {
            List<Map<String,Object>> humpDatas = new ArrayList<>();
            for (Map<String,Object> dataItem:dbListResult.getData()) {
                Map<String,Object> humpDataItem = objectKeytoHump(dataItem);
                if(BeanUtil.isNotEmpty(humpDataItem)){
                    humpDatas.add(humpDataItem);
                }

            }
            pageBean.setData(humpDatas);
        }

        return pageBean;
    }

    @Override
    public RestResponse delData(String formId,String dataId ){
        FormVisualdevEntity bean = formVisualdevService.getById(formId);
        if(BeanUtil.isEmpty(bean) || StringUtil.isEmpty(bean.getDbLinkId())){
            return null;
        }
        DbConfig dbConfig = dataSourceCache.queryJdbcById(bean.getDbLinkId());
        JdbcSql jdbcSql = new JdbcSql(dbConfig);
        if(BeanUtil.isEmpty(dbConfig)){
            return null;
        }

        //主表名
        String masterTable=null;
        DbTableVo dbTableVo = null;
        JSONArray tableJsonArray  = JSONArray.parseArray(bean.getTemTable());
        if(BeanUtil.isNotEmpty(tableJsonArray)){
            for (int i = 0; i <tableJsonArray.size() ; i++) {
                JSONObject item = (JSONObject) tableJsonArray.get(i);
                if(item.get("typeId").equals(1)){
                    masterTable = item.get("tableName").toString();
                }
            }
        }
        if(StringUtil.isEmpty(masterTable)){
            RestResponse.error("获取主表为空");
        }
        if(StringUtil.isEmpty(dataId)){
            RestResponse.error("dataId不能为空");
        }

        //定义主sql
        StringBuffer mainSql = new StringBuffer();
        mainSql.append("delete ");
        //拼接表名
        mainSql.append(" from ").append(masterTable);
        //设置主键为查询条件
        mainSql.append(" where ").append(ID_FIELD).append(" =? ");
        List<Object> datas = new ArrayList<>();
        datas.add(dataId);
        //获取总记录数
        try{
            jdbcSql.executeUpdate(mainSql.toString(),datas.toArray());
        }catch (Exception e){
            e.printStackTrace();
            return RestResponse.error("执行删除数据出错");
        }

        return RestResponse.success();
    }

    @Override
    public Map<String, Object> getDataItem(String formId, String dataId) {
        FormVisualdevEntity bean = formVisualdevService.getById(formId);
        if(BeanUtil.isEmpty(bean) || StringUtil.isEmpty(bean.getDbLinkId())){
            return null;
        }
        DbConfig dbConfig = dataSourceCache.queryJdbcById(bean.getDbLinkId());
        JdbcSql jdbcSql = new JdbcSql(dbConfig);
        if(BeanUtil.isEmpty(dbConfig)){
            return null;
        }

        //主表名
        String masterTable=null;
        DbTableVo dbTableVo = null;
        JSONArray tableJsonArray  = JSONArray.parseArray(bean.getTemTable());
        if(BeanUtil.isNotEmpty(tableJsonArray)){
            for (int i = 0; i <tableJsonArray.size() ; i++) {
                JSONObject item = (JSONObject) tableJsonArray.get(i);
                if(item.get("typeId").equals(1)){
                    masterTable = item.get("tableName").toString();
//                    dbTableVo = JSONObject.parseObject(String.valueOf(item),DbTableVo.class);
                }
            }
        }

        //定义主sql
        StringBuffer mainSql = new StringBuffer();
        mainSql.append("select * ");
        //拼接表名
        mainSql.append(" from ").append(masterTable);
        //设置主键为查询条件
        mainSql.append(" where ").append(ID_FIELD).append("='").append(dataId).append("'");
        //获取总记录数
        DbResult dbListResult = jdbcSql.getSqlData(mainSql.toString());
        if (dbListResult != null && dbListResult.getData() != null) {
            List<Map<String,Object>> dataList = dbListResult.getData();
            //获取第一条数据
            Map<String,Object> dataItem = dataList.get(0);
            dataItem = objectKeytoHump(dataItem);
            return dataItem;
        }
        return null;
    }

    @Override
    public RestResponse insertOrEditDataItem(DataRequest dataRequest) {
        FormVisualdevEntity bean = formVisualdevService.getById(dataRequest.getFormId());
        if(BeanUtil.isEmpty(bean)){
            return RestResponse.error("查不到表单配置信息");
        }
        if(StringUtil.isEmpty(bean.getDbLinkId())){
            return RestResponse.error("DbLinkId为空");
        }
        DbConfig dbConfig = dataSourceCache.queryJdbcById(bean.getDbLinkId());
        JdbcSql jdbcSql = new JdbcSql(dbConfig);
        if(BeanUtil.isEmpty(dbConfig)){
            return RestResponse.error("查不到关联数据库信息");
        }
        //主表名
        String masterTable=null;
        DbTableVo dbTableVo = null;
        JSONArray tableJsonArray  = JSONArray.parseArray(bean.getTemTable());
        if(BeanUtil.isNotEmpty(tableJsonArray)){
            for (int i = 0; i <tableJsonArray.size() ; i++) {
                JSONObject item = (JSONObject) tableJsonArray.get(i);
                if(item.get("typeId").equals(1)){
                    masterTable = item.get("tableName").toString();
                }
            }
        }

        JdbcMeta jdbcMeta = new JdbcMeta(dbConfig);
        dbTableVo = jdbcMeta.getTableWithColumn(dbConfig.getDbName(), masterTable);
        //主表字段
        List<ColumnVo> columnVoList = new ArrayList<>();
        if((dbTableVo.getColumns() != null) && (dbTableVo.getColumns().size() > 0)){
            List<DbColumn> columns = dbTableVo.getColumns();
            columnVoList = columns.stream().map(column ->{
                ColumnVo columnVo =new ColumnVo();
                columnVo.setColumnName(column.getColumnName());
                columnVo.setColumnHumpName(column.getColumnHumpName());
                columnVo.setRemarks(column.getRemarks());
                if(BeanUtil.isNotEmpty(dataRequest.getJsonObject()) && BeanUtil.isNotEmpty(column.getColumnName())  ){
                    if(BeanUtil.isNotEmpty(dataRequest.getJsonObject().get(column.getColumnName())) ){
                        Object value =dataRequest.getJsonObject().get(column.getColumnName());
                        Object newValue = dataTypeConversion(value,column);
                        columnVo.setValue(newValue.toString());
                    }else if (BeanUtil.isNotEmpty(dataRequest.getJsonObject().get(column.getColumnHumpName()))){
                        Object value =dataRequest.getJsonObject().get(column.getColumnHumpName());
                        Object newValue = dataTypeConversion(value,column);
                        columnVo.setValue(newValue.toString());
                    }
                }
                return columnVo;
            }).filter(vo -> vo.getValue() != null).collect(Collectors.toList());

        }

        if(dataRequest.getType().equals("2")){
            //编辑数据保存
            JdbcUpdateParams jdbcUpdateParams = new JdbcUpdateParams();
            jdbcUpdateParams.setLinkTable(masterTable);
            //默认主键id
            jdbcUpdateParams.setIdKey(ID_FIELD);
            jdbcUpdateParams.setIdValue(dataRequest.getJsonObject().get(ID_FIELD).toString());
            //修改字段列
            List<String> updateColumns = new ArrayList<>();
            //定义数据存储列
            List<Object> cloumnValues = new ArrayList<Object>();
            for (ColumnVo columnVo:columnVoList) {
                updateColumns.add(columnVo.getColumnName());
                cloumnValues.add(columnVo.getValue());
            }
            jdbcUpdateParams.setColumn(updateColumns);
            jdbcUpdateParams.setDatas(cloumnValues);
            //执行数据修改
            JdbcParamUtil.executeViewUpdate(jdbcUpdateParams,jdbcSql);


        }else {
            //新增数据保存
            JdbcInsertParams jdbcInsertParams = new JdbcInsertParams();
            //新增字段列
            List<String> addColumns = new ArrayList<>();
            //定义数据存储列
            List<Object> cloumnValues = new ArrayList<Object>();
            for (ColumnVo columnVo:columnVoList) {
                addColumns.add(columnVo.getColumnName());
                cloumnValues.add(columnVo.getValue());
            }
            jdbcInsertParams.setLinkTable(masterTable);
            String uuid = UUID.randomUUID().toString();
            uuid = uuid.replace("-", "");
            List<String> idValues = new ArrayList<>();
            idValues.add(uuid);
            jdbcInsertParams.setIdValues(idValues);
            jdbcInsertParams.setColumn(addColumns);
            List<List<Object>> datas = new ArrayList<>();
            datas.add(cloumnValues);
            jdbcInsertParams.setDatas(datas);

            //执行数据新增
            JdbcParamUtil.executeViewInsert(jdbcInsertParams,jdbcSql);


        }
        return RestResponse.success();
    }



    //字段名转驼峰

    public static Map<String,Object> objectKeytoHump(Map<String,Object> dataItem){
        Map<String,Object> humpDataItem= null;
        //字段名转驼峰
        if(BeanUtil.isNotEmpty(dataItem)){
            humpDataItem= new HashMap<>();
            for (String key:dataItem.keySet()) {
                String humpKey = NameUtil.toHump(key);
                humpDataItem.put(humpKey,dataItem.get(key));
            }
        }
        return humpDataItem;
    }




    public static Object dataTypeConversion(Object valuesItem, DbColumn column) {
        if(("INT").equals(column.getDataTypeName())){
            valuesItem = Long.parseLong(valuesItem.toString());
//            if (attribute.getDataAccuracy().equals("0")){
//                valuesItem = Long.parseLong(valuesItem.toString());
//            }else {
//                if (BeanUtil.isNotEmpty(valuesItem)){
//                    valuesItem = new BigDecimal(valuesItem.toString());
//                }
//            }
        }else if(("DATETIME").equals(column.getDataTypeName()) || ("DATE").equals(column.getDataTypeName()) ||("TIME").equals(column.getDataTypeName())){
            if(valuesItem instanceof Long){
                valuesItem = new Timestamp((Long) valuesItem);
            }else{
                valuesItem =  formatDateValue(valuesItem);
            }
        }
        return valuesItem;
    }
    public static Object formatDateValue(Object value){
        try {
            value = Timestamp.valueOf(value.toString());
        }catch (IllegalArgumentException e){
            if (10==value.toString().length()){
                SimpleDateFormat format = new SimpleDateFormat(DateUtil.DATE_FORMAT_YMD);
                Date parse = null;
                try {
                    parse = format.parse(value.toString());
                } catch (ParseException parseException) {
                    throw new ParserException("date format must be yyyy-MM-dd");
                }
                String dateStr = new SimpleDateFormat(DateUtil.DATE_FORMAT_FULL).format(parse);
                value = Timestamp.valueOf(dateStr);
            }else {
                throw new IllegalArgumentException("date format must be yyyy-MM-dd or yyyy-mm-dd hh:mm:ss");
            }
        }
        return value;
    }


}
